OpenEdge Data Management:<br />DataServer for ODBC Running a stored procedure

Release 10.1A: OpenEdge Data Management:
DataServer for ODBC

Running a stored procedure

The Progress 4GL statement RUN STORED–PROC allows you to run a stored procedure in the ODBC data source. You must also indicate the end of a stored procedure in your Progress procedure by using the CLOSE STORED–PROC statement.

This is the syntax for the RUN STORED–PROC statement:

Syntax

RUN STORED-PROC procedure [ NO-ERROR ]
[ ( [ OUTPUT ] parameter , ... [ OUTPUT ] parameter ) ]

This is the partial syntax for the CLOSE STORED–PROC statement:
Syntax
CLOSE STORED-PROC procedure 
For example, the following Progress 4GL code runs the stored procedure pcust:
DEFINE VAR handle1 AS int.
RUN STORED-PROC pcust handle1 = PROC-HANDLE (20, OUTPUT 0, OUTPUT 0).
FOR EACH proc-text-buffer:
 DISPLAY proc-text-buffer.
END.
CLOSE STORED-PROC pcust WHERE PROC-HANDLE = handle1.
DISPLAY pcust.orders pcust.states. 
This code first defines an integer variable named handle1 that serves as a handle for identifying the stored procedure. If you have only one active stored procedure, you do not have to specify a handle. However, it is good programming practice to use handles to identify all of your stored procedures.

It then runs the stored procedure as follows:

The pcust stored procedure passes the values 20, 0, and 0 to the three parameters (specifying orders and states as output parameters).

Using a FOR EACH statement, it reads the results into the Progress-supplied buffer proc–text–buffer.

It displays the results.

The Progress procedure next uses the CLOSE STORED–PROC statement to fetch the orders and states output parameters and then displays them. Note that the stored procedure does not return output parameter values unless you request them with the keyword OUTPUT or INPUT–OUTPUT when you execute the procedure.

You can close all stored procedures at once with the following statement:
RUN STORED-PROC closeallprocs. 
Note: For Sybase, the DataServer typically maintains one connection. If your application requires that you process other queries while a stored procedure is open, use the -Dsrv qt_separate_connection parameter or the QUERY–TUNING (SEPARATE–CONNECTION) option to specify that the DataServer use a separate connection for each statement that requires a cursor.

See Appendix B, "Stored Procedure Reference," for a description of the complete syntax for the Progress statements and functions that support running stored procedures.